package com.dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

@Repository
public interface StatisticsDao {

    /*
    设备的总维修费用
     */
    @Select("select sum(maintenance_order.final_price) " +
            "from maintenance_order, equipment " +
            "where maintenance_order.equipment_id = #{equipment_id} " +
            "and maintenance_order.equipment_id = equipment.equipment_id " +
            "and (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10)")
    public Float maintenancePriceOfEquipment (Integer equipment_id);


    /*
    设备某年的维修费用
     */
    @Select("select sum(maintenance_order.final_price) from " +
            "maintenance_order, equipment, maintenance_process " +
            "where maintenance_order.equipment_id = #{equipment_id} " +
            "and maintenance_order.equipment_id = equipment.equipment_id " +
            "and maintenance_order.id = maintenance_process.order_id " +
            "and maintenance_process.task_id = 1 " +
            "and (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and date_format(maintenance_process.finished_time,'%Y') = '${year}'")
    public Float maintenancePriceOfEquipmentYear(@Param("equipment_id")Integer equipment_id,
                                                 @Param("year") Integer year);

    /*
    设备某年的维修次数
     */
    @Select("select count(maintenance_order.id) from " +
            "maintenance_order, equipment, maintenance_process " +
            "where maintenance_order.equipment_id = #{equipment_id} " +
            "and maintenance_order.equipment_id = equipment.equipment_id " +
            "and maintenance_order.id = maintenance_process.order_id " +
            "and maintenance_process.task_id = 1 " +
            "and (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and date_format(maintenance_process.finished_time,'%Y') = '${year}'")
    public Integer maintenanceTimesOfEquipmentYear(@Param("equipment_id")Integer equipment_id,
                                                 @Param("year") Integer year);

    /*
    装备部门的总维修费用
     */
    @Select("select sum(maintenance_order.final_price) from " +
            "maintenance_order where " +
            "(maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and maintenance_order.department_id = 2")
    public Float maintenancePriceOfEquip();

    /*
    装备部门某年的维修费用
     */
    @Select("select sum(maintenance_order.final_price) " +
            "from maintenance_order, maintenance_process " +
            "where maintenance_order.department_id = 2 " +
            "and maintenance_order.id = maintenance_process.order_id " +
            "and maintenance_process.task_id = 1 " +
            "and (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and date_format(maintenance_process.finished_time,'%Y') = '${year}'")
    public Float maintenancePriceOfEquipYear(@Param("year") Integer year);

    /*
   装备部门某年的维修次数
    */
    @Select("select count(maintenance_order.id) " +
            "from maintenance_order, maintenance_process " +
            "where maintenance_order.department_id = 2 " +
            "and maintenance_order.id = maintenance_process.order_id " +
            "and maintenance_process.task_id = 1 " +
            "and (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and date_format(maintenance_process.finished_time,'%Y') = '${year}'")
    public Integer maintenanceTimesOfEquipYear(@Param("year") Integer year);


    /*
    单位的总维修费用
     */
    @Select("select sum(maintenance_order.final_price) " +
            "from maintenance_order, maintenance_process, `user`, user_insiderunit " +
            "where maintenance_order.id = maintenance_process.order_id " +
            "and maintenance_process.task_id = 1 " +
            "and (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and maintenance_process.transactor_id =`user`.id " +
            "and `user`.id = user_insiderunit.user_id " +
            "and user_insiderunit.insiderUnit_id = #{insiderUnit_id}")
    public Float maintenancePriceOfInsiderUnit(Integer insiderUnit_id);


    /*
    单位某年的维修费用
     */
    @Select("select sum(maintenance_order.final_price) " +
            "from maintenance_order, maintenance_process, `user`, user_insiderunit " +
            "where maintenance_order.id = maintenance_process.order_id " +
            "and maintenance_process.task_id = 1 " +
            "and (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and maintenance_process.transactor_id =`user`.id " +
            "and `user`.id = user_insiderunit.user_id " +
            "and user_insiderunit.insiderUnit_id = #{insiderUnit_id} " +
            "and date_format(maintenance_process.finished_time,'%Y') = '${year}'")
    public Float maintenancePriceOfInsiderUnitYear(@Param("insiderUnit_id") Integer insiderUnit_id,
                                                   @Param("year") Integer year);


    /*
    维修方总维修费用
     */
    @Select("select sum(maintenance_order.final_price) " +
            "from maintenance_order, `user`, user_role " +
            "where (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and maintenance_order.unit_id = `user`.id " +
            "and `user`.id = user_role.user_id " +
            "and user_role.role_id = -1")
    public Float maintenancePriceOfMaintenanceUnit();

    /*
    维修方总维修次数
     */
    @Select("select count(maintenance_order.id) " +
            "from maintenance_order, `user`, user_role " +
            "where (maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10) " +
            "and maintenance_order.unit_id = `user`.id " +
            "and `user`.id = user_role.user_id " +
            "and user_role.role_id = -1")
    public Integer maintenanceTimesOfMaintenanceUnit();

    /*
    维修方某年的总维修费用
     */
    @Select("SELECT sum(maintenance_order.final_price) " +
            "FROM maintenance_order,`user`,user_role,maintenance_process " +
            "WHERE( maintenance_order.order_state = 8 OR maintenance_order.order_state = 9 OR maintenance_order.order_state = 10 ) " +
            "AND maintenance_order.unit_id = `user`.id " +
            "AND `user`.id = user_role.user_id " +
            "AND user_role.role_id = - 1 " +
            "AND maintenance_process.order_id = maintenance_order.id " +
            "AND maintenance_process.task_id = 1 " +
            "AND date_format( maintenance_process.finished_time, '%Y' ) = '${year}'")
    public Float maintenancePriceOfMaintenanceUnitYear(@Param("year")Integer year);

    /*
    维修方某年的总维修次数
     */
    @Select("SELECT count(maintenance_order.id) "  +
            "FROM maintenance_order,`user`,user_role,maintenance_process " +
            "WHERE( maintenance_order.order_state = 8 OR maintenance_order.order_state = 9 OR maintenance_order.order_state = 10 ) " +
            "AND maintenance_order.unit_id = `user`.id " +
            "AND `user`.id = user_role.user_id " +
            "AND user_role.role_id = - 1 " +
            "AND maintenance_process.order_id = maintenance_order.id " +
            "AND maintenance_process.task_id = 1 " +
            "AND date_format( maintenance_process.finished_time, '%Y' ) = '${year}'")
    public Integer maintenanceTimesOfMaintenanceUnitYear(@Param("year")Integer year);


}
